﻿using CBS.Common.Office;
using CBS.DB;
using CBS.DB.CBSEntity;
using CBS.Model.ExcelModel;
using CBS.Model.ManageModel;
using CBS.Model.UIModel;
using CBS.Model.ViewModel;
using MathNet.Numerics.Distributions;
using MySqlConnector;
using NPOI.HPSF;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Security.Cryptography.Xml;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace CBS.APP
{
    public class StudentSignUpApp
    {
        public static bool AddStudentSignUp(StudentSignUpViewModel studentSignUp, out string msg)
        {
            msg = "";
            using CBSContext cbs = new CBSContext();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                var signUpStudentId = cbs.cbs_student_signup.Where(t => t.CoursePlanId == studentSignUp.CoursePlanId).Select(t => t.StudentId).ToList();
                if (signUpStudentId.Count > 0)
                {
                    var students = cbs.cbs_student.Where(t => t.StudentName == studentSignUp.StudentName).Select(t => t.StudentId).ToList();
                    if (students.Count > 0)
                    {
                        foreach (var item in students)
                        {
                            if (signUpStudentId.Contains(item))
                            {
                                msg = "同一个课程计划下，同年级，同班级，同课程，已经存在同名学生，请重试。";
                                return false;
                            }
                        }
                    }
                }

                var existsStudents = cbs.cbs_student.FirstOrDefault(t => t.StudentName == studentSignUp.StudentName);
                cbs_student student = new();
                if (existsStudents == null)
                {

                    student.StudentName = studentSignUp.StudentName;
                    student.PhoneNumber = studentSignUp.PhoneNumber;
                    student.WxCode = studentSignUp.WxCode;
                    student.FirstJoinSemester = studentSignUp.SemesterId;

                    cbs.cbs_student.Add(student);
                    cbs.SaveChanges();

                    cbs_student_amount amount = new() { StudentId = student.StudentId, Balance = 0 };
                    cbs.cbs_student_amount.Add(amount);

                    if (studentSignUp.IntroduceStudentId != 0)
                    {
                        cbs_student_bonus bonus = new()
                        {
                            SemesterId = studentSignUp.SemesterId,
                            StudentId = student.StudentId,
                            IntroduceStudentId = studentSignUp.IntroduceStudentId
                        };
                        cbs.cbs_student_bonus.Add(bonus);
                        cbs.SaveChanges();
                    }
                }
                else
                {
                    student = existsStudents;
                }

                cbs_student_signup signup = new()
                {
                    CoursePlanId = studentSignUp.CoursePlanId,
                    StudentId = student.StudentId,
                };
                cbs.cbs_student_signup.Add(signup);
                cbs.SaveChanges();

                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                transaction.Rollback();
                return false;
            }

        }

        public static List<StudentSignUpViewModel> GetList(Pagination page, int semesterId, int gradeId, int classId, int courseId, string keyword)
        {
            string sql = @"select a.*,c.StudentId,c.StudentName,d.SemesterName,e.GradeName,f.ClassName,g.CourseName
from cbs_course_plan a
	inner join cbs_student_signup b on a.CoursePlanId = b.CoursePlanId
	left join cbs_student c on b.StudentId = c.StudentId
	left join cbs_semester d on a.SemesterId = d.SemesterId
	left join cbs_Grade e on a.GradeId = e.GradeId
	left join cbs_class f on a.ClassId = f.ClassId
	left join cbs_course g on a.CourseId = g.CourseId
where a.SemesterId = @semesterId
and (@gradeId = 0 or a.GradeId = @gradeId)
and (@classId = 0 or a.ClassId = @classId)
and (@courseId = 0 or a.CourseId = @courseId)
and (@keyword = '' or c.StudentName like @keyword or c.PhoneNumber like @keyword or c.WxCode like @keyword)";

            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@gradeId",gradeId),
                new MySqlParameter("@classId",classId),
                new MySqlParameter("@courseId",courseId),
                new MySqlParameter("@keyword",$"%{keyword}%"),
            };

            return DBHelper.FindListOrderBy<StudentSignUpViewModel>(sql, page, FromPositionInSql.First, para);
        }

        public static List<StudentSelectModel> GetSelectList(int semesterId)
        {
            CBSContext cbs = new();
            return (from courp in cbs.cbs_course_plan
                    join signup in cbs.cbs_student_signup on courp.CoursePlanId equals signup.CoursePlanId
                    join stu in cbs.cbs_student on signup.StudentId equals stu.StudentId
                    where courp.SemesterId == semesterId
                    select new StudentSelectModel
                    {
                        StudentId = signup.StudentId,
                        StudentName = stu.StudentName
                    }).ToList();
        }

        public static bool CheckDoubleSignUp(int coursePlanId, string studentName)
        {
            using CBSContext cbs = new();

            var signUpStudentId = cbs.cbs_student_signup.Where(t => t.CoursePlanId == coursePlanId).Select(t => t.StudentId).ToList();
            if (signUpStudentId.Count > 0) return false;

            var students = cbs.cbs_student.Where(t => t.StudentName == studentName).Select(t => t.StudentId).ToList();
            if (students.Count > 0) return false;

            foreach (var item in students)
            {
                if (signUpStudentId.Contains(item))
                {
                    return true;
                }
            }

            return false;
        }

        public static bool CheckStudentName(string studentName)
        {
            using CBSContext cbs = new();

            var students = cbs.cbs_student.Where(t => t.StudentName == studentName).Select(t => t.StudentId).ToList();
            if (students.Count > 0) return true;

            return false;
        }


        public static bool DeleteStudentSignUp(int studentId, int coursePlanId)
        {
            using CBSContext cbs = new();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                var signup = cbs.cbs_student_signup.FirstOrDefault(t => t.CoursePlanId == coursePlanId && t.StudentId == studentId);
                cbs.cbs_student_signup.Remove(signup);
                cbs.SaveChanges();

                var anotherSignup = (from courp in cbs.cbs_course_plan
                                     join sup in cbs.cbs_student_signup on courp.CoursePlanId equals sup.CoursePlanId
                                     where sup.StudentId == studentId
                                     select sup).ToList();

                //检查学生是不是已经介绍了人 ，如果介绍了人 ，则学生信息不能删除，只能删除报到信息
                var bonus = cbs.cbs_student_bonus.Where(t => t.IntroduceStudentId == studentId).ToList();

                //检查学生账户余额，如果余额不为0.则学生信息不能删除
                var amount = cbs.cbs_student_amount.FirstOrDefault(t => t.StudentId == studentId);

                if (!cbs.cbs_student_sign_detail.Any(t => t.StudentId == studentId) && (anotherSignup.Count == 0 || bonus.Count == 0 || amount.Balance == 0))
                {
                    var student = cbs.cbs_student.FirstOrDefault(t => t.StudentId == studentId);

                    cbs.cbs_student_amount.Remove(amount);
                    cbs.cbs_student.Remove(student);

                    cbs.SaveChanges();

                }

                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return false;
            }

        }

        public static bool BatchDeleteSignUp(int semesterId, int gradeId, int classId, int courseId, string key) 
        {
            using CBSContext cbs = new();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                var signin = (from courp in cbs.cbs_course_plan
                              join siup in cbs.cbs_student_signup on courp.CoursePlanId equals siup.CoursePlanId
                              join stu in cbs.cbs_student on siup.StudentId equals stu.StudentId
                              join less in cbs.cbs_lesson on courp.CoursePlanId equals less.CoursePlanId
                              join siin in cbs.cbs_student_sign_detail on new { less.LessonId, siup.StudentId } equals new { siin.LessonId, siin.StudentId }
                              where courp.SemesterId == semesterId
                              && (gradeId == 0 || courp.GradeId == gradeId)
                              && (classId == 0 || courp.ClassId == classId)
                              && (courseId == 0 || courp.CourseId == courseId)
                              && (key == "" || stu.StudentName.Contains(key))
                              select new { courp.CoursePlanId, siup, stu.StudentId }
                              ).ToList();

                if (signin.Count > 0)
                {
                    transaction.Rollback();
                    return false;
                }

                var signup = (from courp in cbs.cbs_course_plan
                              join siup in cbs.cbs_student_signup on courp.CoursePlanId equals siup.CoursePlanId
                              join stu in cbs.cbs_student on siup.StudentId equals stu.StudentId
                              where courp.SemesterId == semesterId
                              && (gradeId == 0 || courp.GradeId == gradeId)
                              && (classId == 0 || courp.ClassId == classId)
                              && (courseId == 0 || courp.CourseId == courseId)
                              && (key == "" || stu.StudentName.Contains(key))
                              select new { courp.CoursePlanId, siup, stu.StudentId }
                              ).ToList();

                var signupData = signup.Select(t=>t.siup).ToList();
                cbs.cbs_student_signup.RemoveRange(signupData);
                cbs.SaveChanges();

                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        public static bool ImportStudentSignUp(int semesterId, int coursePlanId, string filePath, out string msg)
        {
            msg = string.Empty;

            ////文件后缀名  
            var filePostfixName = filePath.Substring(filePath.LastIndexOf('.') + 1);
            //验证导入格式
            DataTable dtFirstRow = new DataTable();
            try
            {
                dtFirstRow = ExcelHelper.GetFirstRowData(filePath, filePostfixName);
            }
            catch (Exception ex)
            {
                if (ex.Message.IndexOf("表头不允许为空") > -1)
                {
                    msg = ex.Message;
                    return false;
                }
                msg = "文档不符合要求，请检查。";
                return false;
            }


            DataTable dt = new DataTable();
            dt = ExcelHelper.ImportBase(filePath, filePostfixName);

            if (dt == null || dt.Rows.Count == 0)
            {
                msg = "该文件没有内容；或关闭文件“筛选”功能，重试。";
                return false;
            }

            List<StudentSignUpExcelModel> StudentSignUpInfo = ImportDataTransformToExcelModel(dt);

            int studentSignUpCount, studentCount;
            ImportStudent(semesterId, coursePlanId, StudentSignUpInfo, out msg);

            return true;
        }

        public static bool ImportStudent(int semesterId, int coursePlanId, List<StudentSignUpExcelModel> importData, out string msg)
        {
            msg = "";
            //验证导入数据
            List<StudentSignUpExcelModel> confirmData = importData.Where(t => string.IsNullOrEmpty(t.error)).ToList();


            using CBSContext cbs = new();
            using var tran = cbs.Database.BeginTransaction();
            try
            {

                var stuData = (from list in importData
                               select new cbs_student
                               {
                                   StudentName = list.StudentName,
                                   PhoneNumber = list.PhoneNumber,
                                   WxCode = list.WxCode,
                                   FirstJoinSemester = semesterId
                               }).ToList();
                var importName = stuData.Select(t => t.StudentName).ToList();
                //var distinctName = importName.Distinct().ToList();
                HashSet<string> distinctName = new HashSet<string>(importName);
                if (distinctName.Count < importName.Count)
                {
                    msg = "导入文档包含重名学生。";
                    return false;
                }

                var importedData = (from signUp in cbs.cbs_student_signup
                                    join stu in cbs.cbs_student on signUp.StudentId equals stu.StudentId
                                    where signUp.CoursePlanId == coursePlanId
                                    select new
                                    {
                                        classPlanId = signUp.CoursePlanId,
                                        studentId = signUp.StudentId,
                                        studentName = stu.StudentName
                                    }).ToList();

                if (importedData.Count > 0)
                {
                    foreach (var item in importName)
                    {
                        if (importedData.Any(t => t.studentName == item))
                        {
                            msg = "班级已经存在重名学生。";
                            return false;
                        }
                    }

                }

                var allStu = cbs.cbs_student.ToList();
                var allStuName = allStu.Select(t => t.StudentName).ToList();
                var resultStu = stuData.Where(t=> !allStuName.Contains(t.StudentName)).ToList();

                if (resultStu.Count > 0)
                {
                    cbs.cbs_student.AddRange(resultStu);
                    cbs.SaveChanges();

                    var amounts = resultStu.Select(t => new cbs_student_amount { StudentId = t.StudentId, Balance = 0 }).ToList();
                    cbs.cbs_student_amount.AddRange(amounts);
                    cbs.SaveChanges();
                }

                stuData = cbs.cbs_student.Where(t=> importName.Contains(t.StudentName)).ToList();

                var signUpData = stuData.Select(t => new cbs_student_signup { CoursePlanId = coursePlanId, StudentId = t.StudentId }).ToList();
                cbs.cbs_student_signup.AddRange(signUpData);
                cbs.SaveChanges();

                //增加分红数据
                var bonus = confirmData.Where(t => !string.IsNullOrWhiteSpace(t.IntroduceStudentName)).ToList();
                var bonusStu = new List<cbs_student_bonus>();
                if (bonus.Count > 0)
                {
                    //var bonusStuName = bonus.Select(t=>t.SemesterName).ToArray();
                    var introduceStuName = bonus.Select(t => t.IntroduceStudentName).ToArray().Distinct().ToArray();

                    var introduceStu = cbs.cbs_student.Where(t => introduceStuName.Contains(t.StudentName)).ToList();

                    foreach (var item in bonus)
                    {
                        int stuId = stuData.FirstOrDefault(t => t.StudentName == item.StudentName).StudentId;
                        int introduceId = introduceStu.FirstOrDefault(t => t.StudentName == item.IntroduceStudentName).StudentId;
                        bonusStu.Add(new cbs_student_bonus
                        {
                            SemesterId = semesterId,
                            StudentId = stuId,
                            IntroduceStudentId = introduceId,
                            IsSatisfyingCondition = false,
                            IsSettled = false,
                        });
                    }

                    cbs.cbs_student_bonus.AddRange(bonusStu);
                    cbs.SaveChanges();
                }

                tran.Commit();

                return true;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                tran.Rollback();

                return false;
            }

        }

        public static List<StudentSelectModel> GetSignUpStudentList(int SemesterId, int GradeId, int ClassId, int CourseId) 
        {
            using CBSContext cbs = new();

            var data = (from courseplan in cbs.cbs_course_plan
                        join signup in cbs.cbs_student_signup on courseplan.CoursePlanId equals signup.CoursePlanId
                        join student in cbs.cbs_student on signup.StudentId equals student.StudentId
                        where courseplan.SemesterId == SemesterId
                        && (GradeId == 0 || courseplan.GradeId == GradeId)
                        && (ClassId == 0 || courseplan.ClassId == ClassId)
                        && (CourseId == 0 || courseplan.CourseId == CourseId)
                        select new StudentSelectModel 
                        {
                            StudentId = signup.StudentId,
                            StudentName = student.StudentName,
                        }).ToList();

            return data;
        }

        public static List<StudentSignUpExcelModel> ImportDataTransformToExcelModel(DataTable dt)
        {
            List<StudentSignUpExcelModel> list = new List<StudentSignUpExcelModel>();

            List<ImportField> fields = new List<ImportField>()
            {
                new ImportField(){ Name="SemesterName",DisplayName="学期名称"},
                new ImportField() { Name="CoursePlanName",DisplayName="课程计划"},
                new ImportField() { Name="CourseName",DisplayName="科目"},
                new ImportField() { Name="GradeName",DisplayName="年级"},
                new ImportField() { Name="ClassName",DisplayName="班级"},
                new ImportField() { Name="StudentName",DisplayName="学生姓名"},
                new ImportField() { Name="IsFirstSignUp",DisplayName="是否第一次报名"},
                new ImportField() { Name="IntroduceStudentName",DisplayName="介绍人"},
                new ImportField() { Name="PhoneNumber",DisplayName="联系电话"},
                new ImportField() { Name="WxCode",DisplayName="微信号"}
            };
            foreach (DataRow item in dt.Rows)
            {
                bool emptyRow = true;
                for (int i = 0; i < fields.Count; i++)
                {
                    if (!item.IsNull(i))
                    {
                        emptyRow = false;
                        break;
                    }
                }

                if (emptyRow)
                {
                    continue;
                }

                StudentSignUpExcelModel row = new StudentSignUpExcelModel();
                bool haveNullValue = false;
                foreach (var colSet in fields)
                {
                    string fieldViewName = colSet.DisplayName;
                    if (!dt.Columns.Contains(fieldViewName))
                    {
                        continue;
                    }
                    PropertyInfo col = typeof(StudentSignUpExcelModel).GetProperty(colSet.Name);
                    object fieldValue = new object();
                    if (!col.PropertyType.IsGenericType)
                        fieldValue = string.IsNullOrWhiteSpace(item.Field<string>(fieldViewName)) ? null : Convert.ChangeType(item.Field<string>(fieldViewName).Trim(), col.PropertyType);
                    else //泛型Nullable<>
                    {
                        Type genericTypeDefinition = col.PropertyType.GetGenericTypeDefinition();
                        if (genericTypeDefinition == typeof(Nullable<>))
                        {
                            fieldValue = string.IsNullOrWhiteSpace(item.Field<string>(fieldViewName)) ? new Nullable<int>() : Convert.ToInt32(item.Field<string>(fieldViewName).Trim());
                        }

                    }

                    col.SetValue(row, fieldValue, null);
                }

                list.Add(row);
            }
            return list;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="examId"></param>
        /// <returns></returns>
        public static string ValidExcel(DataTable dt, int examId)
        {



            return "";
        }


    }
}
